/*
 * SubSonic - http://subsonicproject.com
 * 
 * The contents of this file are subject to the Mozilla Public
 * License Version 1.1 (the "License"); you may not use this file
 * except in compliance with the License. You may obtain a copy of
 * the License at http://www.mozilla.org/MPL/
 * 
 * Software distributed under the License is distributed on an 
 * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 * implied. See the License for the specific language governing
 * rights and limitations under the License.
*/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using SubSonic.Utilities;

namespace SubSonic
{
    /// <summary>
    /// Summary for the Scaffold class
    /// </summary>
    public class Scaffold : Control, INamingContainer
    {
        private const string ORDER_BY = "ORDER_BY";
        private const string SCAFFOLD_MODE = "SCAFFOLD_MODE";
        private const string SORT_DIRECTION = "SORT_DIRECTION";
        private readonly List<string> _hiddenEditorColumnList = new List<string>();
        private readonly List<string> _hiddenGridColumnList = new List<string>();
        private readonly List<string> _readOnlyColumnList = new List<string>();
        private readonly Button btnAdd = new Button();
        private readonly Button btnCancel = new Button();
        private readonly Button btnDelete = new Button();
        private readonly Button btnSave = new Button();
        private readonly GridView grid = new GridView();
        private readonly Label lblSorter = new Label();
        private readonly Panel surroundingPanel = new Panel();
        private string _buttonCssClass;
        private string _editTableCssClass;
        private string _editTableItemCaptionCellCssClass;
        private string _editTableItemCssClass;
        private string _editTableLabelCssClass;
        private string _manyToManyMap;
        private bool _showScaffoldCaption = true;
        private string _textBoxCssClass;
        private List<Where> _whereCollection;
        private string _whereExpression = string.Empty;
        private string deleteConfirm = "Delete this record? This action cannot be undone...";
        private TableSchema.Table schema;
        private string tableName = String.Empty;


        #region Properties

        public enum ScaffoldMode
        {
            List,
            Add,
            Edit
        }

        private string _providerName;

        /// <summary>
        /// Gets or sets the name of the provider.
        /// </summary>
        /// <value>The name of the provider.</value>
        [Bindable(true)]
        [Category("Data")]
        [DefaultValue("")]
        public string ProviderName
        {
            get { return _providerName; }
            set { _providerName = value; }
        }

        /// <summary>
        /// Gets or sets the many to many map.
        /// </summary>
        /// <value>The many to many map.</value>
        [Bindable(true)]
        [Category("Data")]
        [DefaultValue("")]
        public string ManyToManyMap
        {
            get { return _manyToManyMap; }
            set { _manyToManyMap = value; }
        }


        private bool _returnOnSave=false;

        /// <summary>
        /// Whether to show the list view on save.
        /// </summary>
        [Bindable(true)]
        [Category("Data")]
        [DefaultValue("")]
        public bool ReturnOnSave {
            get { return _returnOnSave; }
            set { _returnOnSave = value; }
        }
	
        /// <summary>
        /// Gets or sets the name of the table.
        /// </summary>
        /// <value>The name of the table.</value>
        [Bindable(true)]
        [Category("Data")]
        [DefaultValue("")]
        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }

        /// <summary>
        /// Sets the hidden editor columns.
        /// </summary>
        /// <value>The hidden editor columns.</value>
        [Bindable(true)]
        [Category("Data")]
        [Description("A comma delimited list of column names which are not displayed in the Editor.")]
        [DefaultValue("")]
        public string HiddenEditorColumns
        {
            set
            {
                _hiddenEditorColumnList.Clear();
                foreach(string columnName in Utility.Split(value))
                    _hiddenEditorColumnList.Add(columnName.ToLower());
            }
        }

        /// <summary>
        /// Sets the hidden grid columns.
        /// </summary>
        /// <value>The hidden grid columns.</value>
        [Bindable(true)]
        [Category("Data")]
        [Description("A comma delimited list of column names which will not be displayed in the GridView.")]
        [DefaultValue("")]
        public string HiddenGridColumns
        {
            set
            {
                _hiddenGridColumnList.Clear();
                foreach(string columnName in Utility.Split(value))
                    _hiddenGridColumnList.Add(columnName.ToLower());
            }
        }

        /// <summary>
        /// Sets the read only columns.
        /// </summary>
        /// <value>The read only columns.</value>
        [Bindable(true)]
        [Category("Data")]
        [Description("A comma delimited list of column names which are read only.")]
        [DefaultValue("")]
        public string ReadOnlyColumns
        {
            set
            {
                _readOnlyColumnList.Clear();
                foreach(string columnName in Utility.Split(value))
                    _readOnlyColumnList.Add(columnName.ToLower());
            }
        }

        /// <summary>
        /// Gets or sets the where expression.
        /// </summary>
        /// <value>The where expression.</value>
        [Bindable(true)]
        [Category("Data")]
        [Description("An expression which allows filtering the rows which are displayed.")]
        [DefaultValue("")]
        public string WhereExpression
        {
            get { return _whereExpression; }
            set { _whereExpression = value; }
        }

        /// <summary>
        /// Gets or sets the where collection.
        /// </summary>
        /// <value>The where collection.</value>
        [Browsable(false)]
        public List<Where> WhereCollection
        {
            get { return _whereCollection; }
            set { _whereCollection = value; }
        }

        /// <summary>
        /// Gets a value indicating whether this instance can create.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if this instance can create; otherwise, <c>false</c>.
        /// </value>
        private bool CanCreate
        {
            get { return _readOnlyColumnList.Count == 0; }
        }

        /// <summary>
        /// Gets a value indicating whether this instance can delete.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if this instance can delete; otherwise, <c>false</c>.
        /// </value>
        private bool CanDelete
        {
            get { return _readOnlyColumnList.Count == 0; }
        }

        /// <summary>
        /// Gets or sets the delete confirm.
        /// </summary>
        /// <value>The delete confirm.</value>
        [Bindable(true)]
        [Category("Behavior")]
        [DefaultValue("Delete this record? This action cannot be undone...")]
        public string DeleteConfirm
        {
            get { return deleteConfirm; }
            set { deleteConfirm = value; }
        }

        /// <summary>
        /// Gets or sets the CSS class.
        /// </summary>
        /// <value>The CSS class.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class for the div surrounding the scaffold.")]
        [DefaultValue(ScaffoldCSS.WRAPPER)]
        public string CssClass
        {
            get { return surroundingPanel.CssClass; }
            set { surroundingPanel.CssClass = value; }
        }

        /// <summary>
        /// Gets or sets the grid view skin ID.
        /// </summary>
        /// <value>The grid view skin ID.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the skin for GridView.")]
        [DefaultValue("scaffold")]
        public string GridViewSkinID
        {
            get { return grid.SkinID; }
            set { grid.SkinID = value; }
        }

        /// <summary>
        /// Gets or sets the edit table CSS class.
        /// </summary>
        /// <value>The edit table CSS class.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by the HTML table in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_TABLE)]
        public string EditTableCssClass
        {
            get { return _editTableCssClass; }
            set { _editTableCssClass = value; }
        }

        /// <summary>
        /// Gets or sets the edit table item CSS class.
        /// </summary>
        /// <value>The edit table item CSS class.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by editable form field elements in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_ITEM)]
        public string EditTableItemCssClass
        {
            get { return _editTableItemCssClass; }
            set { _editTableItemCssClass = value; }
        }

        /// <summary>
        /// Gets or sets the edit table label CSS class.
        /// </summary>
        /// <value>The edit table label CSS class.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by the table label in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_TABLE_LABEL)]
        public string EditTableLabelCssClass
        {
            get { return _editTableLabelCssClass; }
            set { _editTableLabelCssClass = value; }
        }

        /// <summary>
        /// Gets or sets the button CSS class.
        /// </summary>
        /// <value>The button CSS class.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by all buttons.")]
        [DefaultValue(ScaffoldCSS.BUTTON)]
        public string ButtonCssClass
        {
            get { return _buttonCssClass; }
            set { _buttonCssClass = value; }
        }

        /// <summary>
        /// Gets or sets the text box CSS class.
        /// </summary>
        /// <value>The text box CSS class.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by all TextBox elements in the edit form.")]
        [DefaultValue(ScaffoldCSS.TEXT_BOX)]
        public string TextBoxCssClass
        {
            get { return _textBoxCssClass; }
            set { _textBoxCssClass = value; }
        }

        /// <summary>
        /// Gets or sets the edit table item caption cell CSS class.
        /// </summary>
        /// <value>The edit table item caption cell CSS class.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("Sets the CSS class used by the table cell surrounding edit item captions in the edit form.")]
        [DefaultValue(ScaffoldCSS.EDIT_ITEM_CAPTION)]
        public string EditTableItemCaptionCellCssClass
        {
            get { return _editTableItemCaptionCellCssClass; }
            set { _editTableItemCaptionCellCssClass = value; }
        }

        /// <summary>
        /// Gets or sets a value indicating whether [show scaffold caption].
        /// </summary>
        /// <value><c>true</c> if [show scaffold caption]; otherwise, <c>false</c>.</value>
        [Bindable(true)]
        [Category("Display")]
        [Description("If true, a scaffold caption will be shown")]
        public bool ShowScaffoldCaption
        {
            get { return _showScaffoldCaption; }
            set { _showScaffoldCaption = value; }
        }

        /// <summary>
        /// Gets or sets the primary key value.
        /// </summary>
        /// <value>The primary key value.</value>
        [Browsable(false)]
        public string PrimaryKeyValue
        {
            get { return (string)ViewState["PrimaryKeyValue"]; }
            private set { ViewState["PrimaryKeyValue"] = value; }
        }

        /// <summary>
        /// Gets or sets the mode.
        /// </summary>
        /// <value>The mode.</value>
        public ScaffoldMode Mode
        {
            get
            {
                if(ViewState[SCAFFOLD_MODE] == null)
                    ViewState[SCAFFOLD_MODE] = ScaffoldMode.List;
                return (ScaffoldMode)ViewState[SCAFFOLD_MODE];
            }
            private set { ViewState[SCAFFOLD_MODE] = value; }
        }

        /// <summary>
        /// Gets the grid view.
        /// </summary>
        /// <value>The grid view.</value>
        public GridView GridView
        {
            get { return grid; }
        }

        /// <summary>
        /// Prefixes the specified param.
        /// </summary>
        /// <param name="param">The param.</param>
        /// <returns></returns>
        private string Prefix(string param)
        {
            return Utility.PrefixParameter(param, schema.Provider);
        }

        #endregion


        /// <summary>
        /// Used to apply CSS class values to WebControls. Ensures that no empty classes are applied;
        /// </summary>
        /// <param name="control"></param>
        /// <param name="cssClass"></param>
        private static void ApplyCssClass(WebControl control, string cssClass)
        {
            if(!String.IsNullOrEmpty(cssClass))
                control.CssClass = cssClass;
        }

        /// <summary>
        /// Used to apply class attribute to HtmlControls. Ensures that no empty classes are applied;
        /// </summary>
        /// <param name="control"></param>
        /// <param name="cssClass"></param>
        private static void ApplyCssClass(HtmlControl control, string cssClass)
        {
            if(!String.IsNullOrEmpty(cssClass))
                control.Attributes.Add("class", cssClass);
        }

        /// <summary>
        /// Adds the row.
        /// </summary>
        /// <param name="tbl">The TBL.</param>
        /// <param name="text">The text.</param>
        /// <param name="colspan">The colspan.</param>
        private void AddRow(HtmlTable tbl, string text, int colspan)
        {
            HtmlTableRow tr = new HtmlTableRow();
            tbl.Rows.Add(tr);

            HtmlTableCell td = new HtmlTableCell();
            tr.Cells.Add(td);
            ApplyCssClass(td, EditTableLabelCssClass);

            if(colspan > 0)
                td.ColSpan = colspan;

            td.InnerHtml = text;
        }

        /// <summary>
        /// Adds the row.
        /// </summary>
        /// <param name="tbl">The TBL.</param>
        /// <param name="cellValue1">The cell value1.</param>
        /// <param name="control">The control.</param>
        private void AddRow(HtmlTable tbl, string cellValue1, Control control)
        {
            HtmlTableRow tr = new HtmlTableRow();
            tbl.Rows.Add(tr);

            HtmlTableCell td = new HtmlTableCell();
            tr.Cells.Add(td);

            //label
            ApplyCssClass(td, EditTableItemCaptionCellCssClass);
            td.InnerHtml = "<b>" + cellValue1 + "</b>";

            //control
            HtmlTableCell td2 = new HtmlTableCell();
            tr.Cells.Add(td2);
            ApplyCssClass(td, EditTableItemCssClass);
            td2.Controls.Add(control);
        }

        /// <summary>
        /// Called by the ASP.NET page framework to notify server controls that use composition-based implementation to create any child controls they contain in preparation for posting back or rendering.
        /// </summary>
        protected override void CreateChildControls()
        {
            if(!String.IsNullOrEmpty(TableName))
            {
                schema = DataService.GetSchema(tableName, ProviderName, TableType.Table);
                //decide what to do here
                //if there is an ID passed in
                //show the editor, if not, show the grid

                surroundingPanel.ID = "pnlSurround";
                Controls.Add(surroundingPanel);
                surroundingPanel.Controls.Clear();
                //add in the control bar

                Label lblTitle = new Label();
                lblTitle.ID = "lblTitle";
                surroundingPanel.Controls.Add(lblTitle);
                lblTitle.Text = "<h2>" + schema.DisplayName + " Admin</h2>";
                lblTitle.Visible = ShowScaffoldCaption;

                //add in the button row
                Panel pnlButtons = new Panel();
                pnlButtons.ID = "pnlButtons";
                surroundingPanel.Controls.Add(pnlButtons);

                btnAdd.ID = "btnAdd";
                pnlButtons.Controls.Add(btnAdd);
                btnSave.ID = "btnSave";
                pnlButtons.Controls.Add(btnSave);
                btnCancel.ID = "btnCancel";
                pnlButtons.Controls.Add(btnCancel);
                btnDelete.ID = "btnDelete";
                pnlButtons.Controls.Add(btnDelete);

                foreach(Button button in pnlButtons.Controls)
                    ApplyCssClass(button, ButtonCssClass);

                btnDelete.OnClientClick = "return CheckDelete();";

                btnSave.Text = "Save";
                btnDelete.Text = "Delete";
                btnCancel.Text = "Return";
                btnAdd.Text = "Add";

                btnAdd.Click += btnAdd_Click;
                btnSave.Click += btnSave_Click;
                btnCancel.Click += btnCancel_Click;
                btnDelete.Click += btnDelete_Click;

                btnDelete.Visible = CanDelete;

                bool isAdd = (Mode == ScaffoldMode.Add);
                bool isEdit = (Mode == ScaffoldMode.Edit);
                bool isGrid = (Mode == ScaffoldMode.List);

                Panel pnlEditor = new Panel();
                pnlEditor.ID = "pnlEditor";
                surroundingPanel.Controls.Add(pnlEditor);

                CreateEditor(pnlEditor, isEdit);

                Panel pnlGrid = new Panel();
                pnlGrid.ID = "pnlGrid";
                surroundingPanel.Controls.Add(pnlGrid);

                grid.ID = "grid";
                pnlGrid.Controls.Add(grid);

                grid.Sorting += grid_Sorting;
                grid.RowEditing += grid_RowEditing;
                grid.AllowSorting = true;

                btnAdd.Visible = false;

                if(isGrid)
                {
                    btnAdd.Visible = CanCreate;
                    pnlEditor.Visible = false;
                    pnlGrid.Visible = true;
                    btnSave.Visible = false;
                    btnCancel.Visible = false;
                    btnDelete.Visible = false;
                    BindGrid(String.Empty);
                }

                if(isEdit)
                {
                    pnlEditor.Visible = true;
                    pnlGrid.Visible = false;
                    BindEditor(schema, PrimaryKeyValue);
                    btnSave.Visible = true;
                    btnCancel.Visible = true;
                    btnDelete.Visible = CanDelete;
                }

                if(isAdd)
                {
                    pnlEditor.Visible = true;
                    pnlGrid.Visible = false;
                    btnSave.Visible = true;
                    btnCancel.Visible = true;
                    btnDelete.Visible = false;
                }

                Label lblMessage = new Label();
                lblMessage.ID = "lblMessage";
                surroundingPanel.Controls.Add(lblMessage);

                lblSorter.Text = String.Empty;
                surroundingPanel.Controls.Add(lblSorter);
            }

            ViewState[SCAFFOLD_MODE] = Mode;
            if(Mode != ScaffoldMode.Edit)
                PrimaryKeyValue = "";
        }

        /// <summary>
        /// Shows the message.
        /// </summary>
        /// <param name="message">The message.</param>
        private void ShowMessage(string message)
        {
            Label lblMessage = (Label)FindControl("lblMessage");
            if(lblMessage != null)
                lblMessage.Text = message + " <br><i>" + DateTime.Now + "</i>";
        }

        /// <summary>
        /// Binds the grid.
        /// </summary>
        /// <param name="orderBy">The order by.</param>
        private void BindGrid(string orderBy)
        {
            if(schema != null && schema.PrimaryKey != null)
            {
                Query query = new Query(schema);

                if(!String.IsNullOrEmpty(_whereExpression))
                    query.WHERE(_whereExpression);

                if(_whereCollection != null)
                    query.wheres = _whereCollection;

                string sortColumn = null;
                if(!String.IsNullOrEmpty(orderBy))
                    sortColumn = orderBy;
                else if(ViewState[ORDER_BY] != null)
                    sortColumn = (string)ViewState[ORDER_BY];

                int colIndex = -1;

                if(!String.IsNullOrEmpty(sortColumn))
                {
                    ViewState.Add(ORDER_BY, sortColumn);
                    TableSchema.TableColumn col = schema.GetColumn(sortColumn);
                    if(col == null)
                    {
                        for(int i = 0; i < schema.Columns.Count; i++)
                        {
                            TableSchema.TableColumn fkCol = schema.Columns[i];
                            if(fkCol.IsForeignKey && !String.IsNullOrEmpty(fkCol.ForeignKeyTableName))
                            {
                                TableSchema.Table fkTbl = DataService.GetSchema(fkCol.ForeignKeyTableName, ProviderName, TableType.Table);
                                if(fkTbl != null)
                                {
                                    col = fkTbl.Columns[1];
                                    colIndex = i;
                                    break;
                                }
                            }
                        }
                    }
                    if(col != null && col.MaxLength < 2048)
                    {
                        if(ViewState[SORT_DIRECTION] == null || ((string)ViewState[SORT_DIRECTION]) == SqlFragment.ASC)
                        {
                            if(colIndex > -1)
                                query.OrderBy = OrderBy.Asc(col, SqlFragment.JOIN_PREFIX + colIndex);
                            else
                                query.OrderBy = OrderBy.Asc(col);
                            ViewState[SORT_DIRECTION] = SqlFragment.ASC;
                        }
                        else
                        {
                            if(colIndex > -1)
                                query.OrderBy = OrderBy.Desc(col, SqlFragment.JOIN_PREFIX + colIndex);
                            else
                                query.OrderBy = OrderBy.Desc(col);

                            ViewState[SORT_DIRECTION] = SqlFragment.DESC;
                        }
                    }
                }

                DataTable dt = query.ExecuteJoinedDataSet().Tables[0];
                grid.DataSource = dt;
                grid.AutoGenerateColumns = false;
                grid.Columns.Clear();
                grid.DataKeyNames = new string[] {schema.PrimaryKey.ColumnName};

                CommandField link = new CommandField();
                link.ShowEditButton = true;
                link.EditText = "Edit";

                //HyperLinkField link = new HyperLinkField();

                //link.Text = "Edit";
                //link.DataNavigateUrlFields = new string[] { schema.PrimaryKey.ColumnName };
                //link.DataNavigateUrlFormatString = HttpContext.Current.Request.CurrentExecutionFilePath + "?id={0}";
                grid.Columns.Insert(0, link);

                for(int i = 0; i < schema.Columns.Count; i++)
                {
                    BoundField field = new BoundField();
                    field.DataField = dt.Columns[i].ColumnName;
                    field.SortExpression = dt.Columns[i].ColumnName;
                    //field.SortExpression = Utility.QualifyColumnName(schema.Name, dt.Columns[i].ColumnName, schema.Provider);
                    field.HtmlEncode = false;
                    if(schema.Columns[i].IsForeignKey)
                    {
                        TableSchema.Table fkSchema;
                        if(schema.Columns[i].ForeignKeyTableName == null)
                            fkSchema = DataService.GetForeignKeyTable(schema.Columns[i], schema);
                        else
                            fkSchema = DataService.GetSchema(schema.Columns[i].ForeignKeyTableName, ProviderName, TableType.Table);

                        if(fkSchema != null)
                            field.HeaderText = fkSchema.DisplayName;
                    }
                    else
                        field.HeaderText = schema.Columns[i].DisplayName;

                    if(!Utility.IsAuditField(dt.Columns[i].ColumnName) && !_hiddenGridColumnList.Contains(dt.Columns[i].ColumnName.ToLower()))
                        grid.Columns.Add(field);
                }

                grid.DataBind();
            }
        }

        /// <summary>
        /// Special builder for many to many relational tables.
        /// </summary>
        /// <returns></returns>
        private HtmlTable CreateManyMapper()
        {
            HtmlTable tbl = new HtmlTable();
            tbl.Width = "600px";

            if(ShowScaffoldCaption)
                AddRow(tbl, "<h2>" + schema.DisplayName + " Map </h2>", 2);

            foreach(TableSchema.TableColumn col in schema.Columns)
            {
                //by convention, each key in the map table should be a foreignkey
                //if not, it's not good
                if(col.IsPrimaryKey)
                {
                    string fkTable;
                    fkTable = DataService.GetForeignKeyTableName(col.ColumnName, schema.Name, ProviderName);
                    Query qry = new Query(DataService.GetSchema(fkTable, ProviderName, TableType.Table));
                    DropDownList ddl = new DropDownList();
                    ddl.ID = col.ColumnName;
                    AddRow(tbl, fkTable, ddl);

                    IDataReader rdr = qry.ExecuteReader();
                    while(rdr.Read())
                        ddl.Items.Add(new ListItem(rdr[1].ToString(), rdr[0].ToString()));
                    rdr.Close();
                }
                else
                {
                    Control ctrl = GetEditControl(col);
                    AddRow(tbl, Utility.ParseCamelToProper(col.ColumnName), ctrl);
                }
            }
            return tbl;
        }

        /// <summary>
        /// Determines whether [is many to many].
        /// </summary>
        /// <returns>
        /// 	<c>true</c> if [is many to many]; otherwise, <c>false</c>.
        /// </returns>
        private bool isManyToMany()
        {
            int keyCount = 0;
            bool bOut = false;

            foreach(TableSchema.TableColumn col in schema.Columns)
            {
                if(col.IsPrimaryKey)
                    keyCount++;
            }

            if(keyCount > 1)
                bOut = true;

            return bOut;
        }

        /// <summary>
        /// Creates the editor.
        /// </summary>
        /// <param name="destination">The destination.</param>
        /// <param name="isEdit">if set to <c>true</c> [is edit].</param>
        private void CreateEditor(Control destination, bool isEdit)
        {
            //if this is a many to many, we need to construct it differently
            HtmlTable tbl = new HtmlTable();
            if(isManyToMany())
            {
                tbl = CreateManyMapper();
                destination.Controls.Add(tbl);
            }
            else
            {
                destination.Controls.Add(tbl);
                tbl.Width = "600px";

                if(ShowScaffoldCaption)
                    AddRow(tbl, "<h2>" + schema.DisplayName + " Editor</h2>", 2);

                foreach(TableSchema.TableColumn col in schema.Columns)
                {
                    Control ctrl = GetEditControl(col);
                    if(ctrl != null)
                    {
                        string label;
                        label = col.DisplayName;
                        AddRow(tbl, label, ctrl);
                        if(_hiddenEditorColumnList.Contains(col.ColumnName.ToLower()))
                            ctrl.Parent.Parent.Visible = false;
                        if(ctrl.GetType() == typeof(TextBox))
                        {
                            TextBox tbx = (TextBox)ctrl;
                            if(tbx.TextMode == TextBoxMode.MultiLine)
                            {
                                int efftectiveMaxLength = Utility.GetEffectiveMaxLength(col);
                                string remainingLength = (efftectiveMaxLength - tbx.Text.Length).ToString();
                                string maxLength = efftectiveMaxLength.ToString();

                                tbx.Attributes.Add("onkeyup", "return imposeMaxLength(event, this, " + maxLength + ", " + tbl.Rows.Count + ");");
                                tbx.Attributes.Add("onChange", "return imposeMaxLength(event, this, " + maxLength + ", " + tbl.Rows.Count + ");");
                                LiteralControl lc =
                                    new LiteralControl("<div style='padding: 2px;'><div style='float:left'>Characters Remaining:&nbsp;</div><div id=\"counter" + tbl.Rows.Count +
                                                       "\" style=\"visibility:hidden\">" + remainingLength + "</div></div>");
                                tbx.Parent.Controls.Add(lc);
                            }
                        }
                    }
                }
                //need a primary key for many/many editing
                if(!String.IsNullOrEmpty(_manyToManyMap) && isEdit)
                    AddManyToMany(isEdit, tbl);
            }
        }

        /// <summary>
        /// Binds the editor.
        /// </summary>
        /// <param name="tbl">The TBL.</param>
        /// <param name="keyID">The key ID.</param>
        private void BindEditor(TableSchema.Table tbl, string keyID)
        {
            //get all the data for this row
            Query qry = new Query(tbl);
            qry.AddWhere(tbl.PrimaryKey.ColumnName, keyID);
            IDataReader rdr = qry.ExecuteReader();

            if(rdr.Read())
            {
                foreach(TableSchema.TableColumn col in tbl.Columns)
                {
                    if(col.IsPrimaryKey)
                    {
                        Control ctrl = FindControl("pkID");
                        if(ctrl != null)
                        {
                            string colValue;
                            colValue = rdr[col.ColumnName].ToString();
                            Type ctrlType = ctrl.GetType();
                            if(ctrlType == typeof(Label))
                                ((Label)ctrl).Text = colValue;
                            else if(ctrlType == typeof(DropDownList))
                                ((DropDownList)ctrl).SelectedValue = colValue;
                            else if(ctrlType == typeof(TextBox))
                                ((TextBox)ctrl).Text = colValue;
                        }
                    }
                    else
                    {
                        Control ctrl = FindControl(col.ColumnName);
                        if(ctrl != null)
                        {
                            Type ctrlType = ctrl.GetType();
                            if(ctrlType == typeof(TextBox))
                            {
                                TextBox tbx = ((TextBox)ctrl);
                                tbx.Text = rdr[col.ColumnName].ToString();
                            }
                            else if(ctrlType == typeof(CheckBox))
                            {
                                if(!col.IsNullable || (col.IsNullable && rdr[col.ColumnName] != DBNull.Value))
                                    ((CheckBox)ctrl).Checked = Convert.ToBoolean(rdr[col.ColumnName]);
                            }
                            else if(ctrlType == typeof(DropDownList))
                                ((DropDownList)ctrl).SelectedValue = rdr[col.ColumnName].ToString();
                            else if(ctrlType == typeof(CalendarControl))
                            {
                                DateTime dt;
                                if(DateTime.TryParse(rdr[col.ColumnName].ToString(), out dt))
                                {
                                    CalendarControl cal = (CalendarControl)ctrl;
                                    cal.SelectedDate = dt.Date;
                                }
                            }
                            else if(ctrlType == typeof(Label))
                                ((Label)ctrl).Text = rdr[col.ColumnName].ToString();
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Adds the many to many.
        /// </summary>
        /// <param name="isEdit">if set to <c>true</c> [is edit].</param>
        /// <param name="tbl">The TBL.</param>
        private void AddManyToMany(bool isEdit, HtmlTable tbl)
        {
            string[] mmTables = _manyToManyMap.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);

            if(mmTables.Length > 0)
            {
                foreach(string mmTableName in mmTables)
                {
                    TableSchema.Table mmTable = Query.BuildTableSchema(mmTableName, ProviderName);

                    //this table should have one or more primary keys
                    //one of these keys should, by convention, have the same name
                    //as the primary key of our main schema table
                    //need to get this key, then find it's table
                    foreach(TableSchema.TableColumn col in mmTable.Columns)
                    {
                        if(col.IsPrimaryKey && col.ColumnName.ToLower() != schema.PrimaryKey.ColumnName.ToLower())
                        {
                            //this is the key we need. Get the table for this key
                            string fTableName = DataService.GetForeignKeyTableName(col.ColumnName, mmTableName, ProviderName);

                            if(!String.IsNullOrEmpty(fTableName) && isEdit)
                            {
                                CheckBoxList chk = new CheckBoxList();
                                chk.ID = mmTableName;
                                //add the checkbox in
                                AddRow(tbl, Utility.ParseCamelToProper(mmTableName), chk);
                                chk.RepeatColumns = 2;

                                TableSchema.Table fTable = Query.BuildTableSchema(fTableName);
                                Query qry = new Query(fTable);
                                IDataReader rdr = qry.ExecuteReader();

                                while(rdr.Read())
                                    chk.Items.Add(new ListItem(rdr[1].ToString(), rdr[0].ToString()));

                                rdr.Close();
                                //now we need to query the map table, loop it, and check off the items
                                //that are in it

                                rdr = new Query(mmTable).AddWhere(schema.PrimaryKey.ColumnName, PrimaryKeyValue).ExecuteReader();

                                //thanks to jcoenen for this!
                                while(rdr.Read())
                                {
                                    string fkID = rdr[fTable.PrimaryKey.ColumnName].ToString();
                                    foreach(ListItem item in chk.Items)
                                    {
                                        if(item.Value.ToLower().Equals(fkID.ToLower()))
                                        {
                                            item.Selected = true;
                                            break;
                                        }
                                    }
                                }

                                rdr.Close();
                            }
                        }
                    }
                }
            }
        }

        /// <summary>
        /// Saves the many to many.
        /// </summary>
        /// <param name="mapTableName">Name of the map table.</param>
        private void SaveManyToMany(string mapTableName)
        {
            //first, need to get the id of the other field
            TableSchema.Table fkTable = Query.BuildTableSchema(mapTableName);
            string fkField = String.Empty;
            foreach(TableSchema.TableColumn col in fkTable.Columns)
            {
                if(col.IsPrimaryKey && col.ColumnName.ToLower() != schema.PrimaryKey.ColumnName.ToLower())
                {
                    fkField = col.ColumnName;
                    break;
                }
            }

            if(!String.IsNullOrEmpty(fkField))
            {
                int pk = Convert.ToInt32(PrimaryKeyValue);
                //first, delete out all references in there
                //this MUST be done in a transaction!
                QueryCommandCollection transCollection = new QueryCommandCollection();

                Query qry = new Query(DataService.GetSchema(mapTableName, ProviderName, TableType.Table));
                qry.QueryType = QueryType.Delete;
                qry.AddWhere(schema.PrimaryKey.ColumnName, pk);

                transCollection.Add(qry.BuildDeleteCommand());

                //now, loop the check list, adding items in for each checked bit
                string sql = "INSERT INTO " + mapTableName + "(" + fkField + "," + schema.PrimaryKey.ColumnName + ") VALUES (" + Prefix("fk") + "," + Prefix("pk)");

                CheckBoxList chk = (CheckBoxList)FindControl(fkTable.Name);
                if(chk != null)
                {
                    foreach(ListItem item in chk.Items)
                    {
                        if(item.Selected)
                        {
                            QueryCommand cmd = new QueryCommand(sql, ProviderName);
                            cmd.Parameters.Add(Prefix("fk"), item.Value, DbType.Int32);
                            cmd.Parameters.Add(Prefix("pk"), pk);
                            transCollection.Add(cmd);
                        }
                    }
                }

                //execute
                DataService.ExecuteTransaction(transCollection);
            }
        }

        /// <summary>
        /// Saves the editor.
        /// </summary>
        private void SaveEditor()
        {
            QueryCommand cmd = new QueryCommand(String.Empty, ProviderName);

            //gotta loop through here, create the proper command for this table
            //and execute
            //see if lblID is 0 or a value
            //if the primary key of the schema table is autoincrement, this will be a label
            string pk;

            //thanks to jcoenen for this!
            if(schema.PrimaryKey.AutoIncrement || schema.PrimaryKey.DataType == DbType.Guid)
                pk = ((Label)FindControl("pkID")).Text;
            else
                pk = ((TextBox)FindControl("pkID")).Text;

            if(Mode == ScaffoldMode.Edit)
            {
                cmd.CommandSql = BuildUpdateSql();
                cmd.Parameters.Add(Prefix(schema.PrimaryKey.ColumnName), pk);
            }
            else
            {
                cmd.CommandSql = BuildInsertSql();
                //if (!schema.PrimaryKey.AutoIncrement) { - GUID fix below
                if(!schema.PrimaryKey.AutoIncrement && schema.PrimaryKey.DataType != DbType.Guid)
                {
                    //have to put this in 
                    cmd.Parameters.Add(Prefix(schema.PrimaryKey.ColumnName), pk);
                }
            }

            if(schema != null)
            {
                bool isAdd = Mode == ScaffoldMode.Add;
                foreach(TableSchema.TableColumn col in schema.Columns)
                {
                    //pull the value from the controls
                    if(col.DataType != DbType.Binary && col.DataType != DbType.Byte && !col.IsPrimaryKey)
                    {
                        Control ctrl = FindControl(col.ColumnName);
                        object oVal = Utility.GetDefaultControlValue(col, ctrl, isAdd, true);

                        if(!col.AutoIncrement)
                            cmd.Parameters.Add(Prefix(col.ColumnName), oVal, col.DataType);
                    }
                }
            }

            //execute it
            DataService.ExecuteQuery(cmd);

            //save down any many/many bits
            if(!String.IsNullOrEmpty(_manyToManyMap))
            {
                string[] mmTables = _manyToManyMap.Split(new char[] {','}, StringSplitOptions.RemoveEmptyEntries);

                foreach(string mmTableName in mmTables)
                    SaveManyToMany(mmTableName);
            }

            //if they've set ReturnOnSave - show the list again
            //thanks PeterShort for the idea :)
            if (ReturnOnSave) {
                Mode = ScaffoldMode.List;
                CreateChildControls();

            }
                

        }

        /// <summary>
        /// Gets the edit control.
        /// </summary>
        /// <param name="col">The col.</param>
        /// <returns></returns>
        private Control GetEditControl(TableSchema.TableColumn col)
        {
            WebControl cOut = null;
            string colName = col.ColumnName.ToLower();
            //use special care with the Primary Key
            if(col.IsPrimaryKey)
            {
                //don't want to edit an auto-increment
                if(col.AutoIncrement || col.DataType == DbType.Guid)
                {
                    Label lblPK = new Label();
                    lblPK.ID = "pkID";
                    cOut = lblPK;
                }
                else
                {
                    TextBox txtPK = new TextBox();
                    txtPK.ID = "pkID";
                    cOut = txtPK;
                }
            }
            else
            {
                if(col.IsForeignKey)
                {
                    DropDownList ddl = new DropDownList();
                    string fkTableName = DataService.GetForeignKeyTableName(col.ColumnName, col.Table.Name, ProviderName);

                    TableSchema.Table tbl = DataService.GetSchema(fkTableName, ProviderName, TableType.Table);
                    Query qry = new Query(tbl);

                    qry.OrderBy = OrderBy.Asc(tbl.Columns[1].ColumnName);

                    IDataReader rdr = qry.ExecuteReader();

                    //load up the dropdown
                    //by convention the descriptor should be the second field

                    if(col.IsNullable)
                    {
                        ListItem liNull = new ListItem("(Not Specified)", String.Empty);
                        ddl.Items.Add(liNull);
                    }

                    while(rdr.Read())
                    {
                        ListItem item = new ListItem(rdr[1].ToString(), rdr[0].ToString());
                        ddl.Items.Add(item);
                    }
                    rdr.Close();

                    cOut = ddl;
                }
                else
                {
                    switch(col.DataType)
                    {
                        case DbType.Guid:
                        case DbType.AnsiString:
                        case DbType.String:
                        case DbType.StringFixedLength:
                        case DbType.Xml:
                        case DbType.Object:
                        case DbType.AnsiStringFixedLength:
                            if(Utility.IsMatch(colName, ReservedColumnName.CREATED_BY) || Utility.IsMatch(colName, ReservedColumnName.MODIFIED_BY))
                                cOut = new Label();
                            else
                            {
                                TextBox t = new TextBox();
                                if(Utility.GetEffectiveMaxLength(col) > 250)
                                {
                                    t.TextMode = TextBoxMode.MultiLine;
                                    t.Columns = 60;
                                    t.Rows = 4;
                                }
                                else
                                {
                                    t.Width = Unit.Pixel(250);
                                    if(colName.EndsWith("guid"))
                                    {
                                        t.Text = Guid.NewGuid().ToString();
                                        t.Enabled = false;
                                    }
                                }
                                cOut = t;
                            }
                            break;

                        case DbType.Binary:
                        case DbType.Byte:
                            //do nothing
                            break;
                        case DbType.Boolean:
                            CheckBox chk = new CheckBox();

                            if(Utility.IsMatch(colName, ReservedColumnName.IS_ACTIVE))
                                chk.Checked = true;

                            if(Utility.IsMatch(colName, ReservedColumnName.DELETED) || Utility.IsMatch(colName, ReservedColumnName.IS_DELETED))
                                chk.Checked = false;

                            cOut = chk;
                            break;

                        case DbType.Date:
                        case DbType.Time:
                        case DbType.DateTime:
                            if(Utility.IsMatch(colName, ReservedColumnName.MODIFIED_ON) || Utility.IsMatch(colName, ReservedColumnName.CREATED_ON))
                                cOut = new Label();
                            else
                                cOut = new CalendarControl();
                            break;

                        case DbType.Int16:
                        case DbType.Int32:
                        case DbType.UInt16:
                        case DbType.Int64:
                        case DbType.UInt32:
                        case DbType.UInt64:
                        case DbType.VarNumeric:
                        case DbType.Single:
                        case DbType.Currency:
                        case DbType.Decimal:
                        case DbType.Double:
                            TextBox tt = new TextBox();
                            tt.Width = Unit.Pixel(50);
                            //if (!this.isNew)
                            //tt.Text = this.GetColumnValue(col.ColumnName).ToString();
                            cOut = tt;
                            break;
                        default:
                            cOut = new TextBox();
                            break;
                    }
                }

                if(cOut != null)
                    cOut.ID = col.ColumnName;
            }
            if(cOut is TextBox)
            {
                TextBox tbx = (TextBox)cOut;
                ApplyCssClass(tbx, TextBoxCssClass);
                if(cOut.GetType() == typeof(TextBox)) //Not Redundant! CalendarControl is TextBox == true; myCalendarControl.GetType() == typeof(TextBox) == false!
                {
                    int maxLength = Utility.GetEffectiveMaxLength(col);
                    if(maxLength > 0)
                        tbx.MaxLength = maxLength;
                }
            }

            if(cOut != null && _readOnlyColumnList.Contains(colName))
                cOut.Enabled = false;

            return cOut;
        }

        /// <summary>
        /// Builds the insert SQL.
        /// </summary>
        /// <returns></returns>
        private string BuildInsertSql()
        {
            string sql = "INSERT INTO " + Utility.QualifyTableName(schema.SchemaName, schema.TableName, schema.Provider);
            string colList = String.Empty;
            string paramList = String.Empty;

            foreach(TableSchema.TableColumn col in schema.Columns)
            {
                if(col.DataType != DbType.Binary && col.DataType != DbType.Byte)
                {
                    if(!col.AutoIncrement && !(col.IsPrimaryKey && col.DataType == DbType.Guid))
                    {
                        colList += col.ColumnName + ",";
                        paramList += Prefix(col.ColumnName) + ",";
                    }
                }
            }

            colList = colList.Remove(colList.Length - 1, 1);
            paramList = paramList.Remove(paramList.Length - 1, 1);

            sql += "(" + colList + ") VALUES (" + paramList + ")";
            return sql;
        }

        /// <summary>
        /// Builds the update SQL.
        /// </summary>
        /// <returns></returns>
        private string BuildUpdateSql()
        {
            string sql = "UPDATE " + Utility.QualifyTableName(schema.SchemaName, schema.TableName, schema.Provider) + " SET ";

            foreach(TableSchema.TableColumn col in schema.Columns)
            {
                if(col.DataType != DbType.Binary && col.DataType != DbType.Byte)
                {
                    if(Utility.IsWritableColumn(col))
                        sql += col.ColumnName + " = " + Prefix(col.ColumnName) + ",";
                }
            }

            sql = sql.Remove(sql.Length - 1, 1);
            sql += " WHERE " + schema.PrimaryKey.ColumnName + " = " + Prefix(schema.PrimaryKey.ColumnName);
            return sql;
        }


        #region Event Handlers

        /// <summary>
        /// Handles the Sorting event of the grid control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
        protected void grid_Sorting(object sender, GridViewSortEventArgs e)
        {
            string columnName = e.SortExpression;
            //rebind the grid
            if(ViewState[SORT_DIRECTION] == null || ((string)ViewState[SORT_DIRECTION]) == SqlFragment.ASC)
                ViewState[SORT_DIRECTION] = SqlFragment.DESC;
            else
                ViewState[SORT_DIRECTION] = SqlFragment.ASC;
            Mode = ScaffoldMode.List;
            BindGrid(columnName);
        }

        /// <summary>
        /// Handles the Click event of the btnCancel control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void btnCancel_Click(object sender, EventArgs e)
        {
            //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath);
            Mode = ScaffoldMode.List;
            CreateChildControls();
        }

        /// <summary>
        /// Handles the Click event of the btnSave control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                SaveEditor();
                //ShowMessage("<font color=\"ForestGreen\"><b>Record Saved</b></font>");
                //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath);
                Mode = ScaffoldMode.List;
                CreateChildControls();
            }
            catch(DbException x)
            {
                ShowMessage("<font color=\"#990000\"><b>" + x.Message + "</b></font>");
            }
        }

        /// <summary>
        /// Handles the Click event of the btnDelete control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void btnDelete_Click(object sender, EventArgs e)
        {
            if(!CanDelete)
                throw new SecurityException(String.Format("This row can not be deleted as it has {0} read-only fields", _readOnlyColumnList.Count));

            string pk;
            if(schema.PrimaryKey.AutoIncrement || (schema.PrimaryKey.DataType == DbType.Guid))
                pk = ((Label)FindControl("pkID")).Text;
            else
                pk = ((TextBox)FindControl("pkID")).Text;

            Query qry = new Query(schema);
            qry.AddWhere(schema.PrimaryKey.ColumnName, pk);
            DataService.ExecuteQuery(qry.BuildDeleteCommand());

            Mode = ScaffoldMode.List;
            CreateChildControls();
            //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath);
        }

        /// <summary>
        /// Handles the Click event of the btnAdd control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            Mode = ScaffoldMode.Add;
            CreateChildControls();
            //HttpContext.Current.Response.Redirect(HttpContext.Current.Request.CurrentExecutionFilePath + "?id=0");
        }

        /// <summary>
        /// Handles the RowEditing event of the grid control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewEditEventArgs"/> instance containing the event data.</param>
        protected void grid_RowEditing(object sender, GridViewEditEventArgs e)
        {
            PrimaryKeyValue = grid.DataKeys[e.NewEditIndex].Value.ToString();
            Mode = ScaffoldMode.Edit;
            CreateChildControls();
        }

        #endregion
    }
}